#!/usr/bin/env python
# -*- coding: utf-8 -*-

__version__ = "$Revision: 0.1 $"
        # $Source$

# Copyright 2010 Pekka Järveläinen
"""
Recursive walk trough filesystem and store exif information and metadata to database. database schema for mysql is available
http://code.google.com/p/exifwalk/wiki/DatabaseDescribtion
"""

import sys, os, MySQLdb
import hashlib
import EXIF
from os.path import join, realpath
OTOS=100000 #the big files are readonly the begin and end
db = MySQLdb.connect(host="localhost", user="pekka", passwd="secret",db="fs")
cursor = db.cursor()
INSERT = "INSERT INTO files (disk, path, md5, mode, ino, dev, nlink, uid, gid, size, atime, mtime, ctime) VALUES (%d, '%s', '%s', %d, %d, %d, %d, %d, %d, %d, %d, %d, %d)"
SELECT = "SELECT no, disk, path from files where md5='%s'"
INSERTDUBLICATE = "INSERT INTO duplicates (md5, no) VALUES ('%s', %d)"
COMMON =  "INSERT INTO exif (no, cameramodel, cameramaker, capturedate, height, width, flash,  exposure_time, Orientation, ISO, ImageNumber, aperture_time, focal_length, fnumber,  SubjectDistance"
COMMONPARAM =  ")  VALUES (%d, '%s', %d, '%s', %d, %d, %d, '%s', %d, %d, %d,  '%s', '%s', '%s',  %d"
INSERTEXIF = COMMON + COMMONPARAM +")"
INSERTEXIFGPS =  COMMON + ", GPSAltitude,  GPSAltitudeRef, GPSLatitude,  GPSLatitudeRef, GPSLongitudeRef,  GPSLongitude "+COMMONPARAM+", %d, %d, '%s', '%s', '%s', '%s')"

def dublicate(cursor, hashhd,  diskno, path):
    """ if same file already is in database add the no to duplicates table
    """
    cursor.execute(SELECT % (hashhd))
    if cursor.rowcount > 0:
        rows = cursor.fetchall()
        hasseen = False
        for row in rows:
            if row[1] == diskno and row[2] == path:
                print "dublicate: ", diskno, path
                hasseen = True
            else:
                insertd = INSERTDUBLICATE % (hashhd, row[0])
                print insertd
                cursor.execute(insertd)
                #print "dublicate %d %s %d %s" % diskno, path, row[0], hashhd
        return hasseen;

def exif(path, f, no):
    """ case jpg file which has exif information add it exif table
    """
    if path[-3:].lower() == 'jpg':
        f.seek( 0 )
        tags = EXIF.process_file(f)
        if not tags:
            print 'No EXIF information found: ', path
        else:
            keys = tags.keys()
            maker = 0
            if 'Image Make' in keys:
                if tags['Image Make'].printable == 'Canon':
                    maker = 1
                elif tags['Image Make'].printable == 'SONY':
                    maker = 3
                elif tags['Image Make'].printable == 'Nokia':
                    maker = 2

            def sure(key, t=1):
                """Check that key exist and
                returns the value: int or string or None"""
                if key in keys:
                    return tags[key].printable
                else:
                    if 0 == t:
                        return 0
                    else:
                        return None

            SubjectDistance = int(sure('MakerNote SubjectDistance', 0))
            ImageNumber = int(sure('MakerNote ImageNumber', 0))
            ISOSpeedRatings = int(sure('EXIF ISOSpeedRatings', 0))
            ApertureValue = None
            if 'EXIF ApertureValue' in keys:
                ApertureValue = tags['EXIF ApertureValue'].printable
            if 'EXIF MaxApertureValue' in keys:
                ApertureValue = tags['EXIF MaxApertureValue'].printable
            time = ""
            if 'EXIF DateTimeDigitized'  in keys:
                time = tags['EXIF DateTimeDigitized'].printable            
            if 'Image DateTime'  in keys:
                time = tags['Image DateTime'].printable
            flash = 0
            if 'EXIF Flash' in keys:
                flash = int(tags['EXIF Flash'].values[0])                       
 
            t = (no, sure('Image Model'),  maker, time, int(tags['EXIF ExifImageLength'].printable),  int(tags['EXIF ExifImageWidth'].printable), flash , sure('EXIF ExposureTime'), int(tags['Image Orientation'].values[0]), ISOSpeedRatings, ImageNumber, ApertureValue, sure('EXIF FocalLength'), sure('EXIF FNumber'), SubjectDistance)
            if 'GPS GPSAltitude' in keys:
                insertlause = INSERTEXIFGPS % t + (int(tags['GPS GPSAltitude'].printable),  int(tags['GPS GPSAltitudeRef'].printable),  tags['GPS GPSLatitude'].printable,  tags['GPS GPSLatitudeRef'].printable,  tags['GPS GPSLongitudeRef'].printable, tags['GPS GPSLongitude'].printable)
            else:
                insertlause = INSERTEXIF % t
            print  insertlause
            cursor.execute(insertlause)


"""main

Commandline arguments:
diskno to store as disk key to dabase
dirnames to recursive walk trough
"""
if __name__ == '__main__':
    if len(sys.argv) == 1:                     # if no dirnames are specified,
        dirnames = os.listdir(os.curdir)      #   use current dir
    else: 
        diskno = int(sys.argv[1])             # otherwise, use dirs specified
        dirnames = sys.argv[2:]               #   on the command line

    for dirname in dirnames:
        for root, dirs, files in os.walk(dirname):
            print root, "contains"
            for name in files:
                realname = realpath(join(root, name))
                mode, ino, dev, nlink, uid, gid, size, atime, mtime, ctime = os.stat(realname)
                f = open(realname, 'rb')
                md5 = hashlib.md5()
                if (size > 20*10**6 ):
                    md5.update(f.read(OTOS))
                    f.seek(size-2*OTOS)
                    md5.update(f.read(OTOS))
                else:
                    md5.update(f.read())
                hashhd = md5.hexdigest()
                dublicate(cursor, hashhd, diskno, realname)
                if not dublicate(cursor, hashhd, diskno, realname):
                    insertlause = INSERT % (diskno, realname, hashhd, mode,ino, dev, nlink, uid, gid, size, atime, mtime, ctime)
                    print "\t", insertlause
                    cursor.execute(insertlause)
                    hasseen = False
                    exif(realname, f, db.insert_id())
                f.close()
